﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Reflection;
 
public partial  class QueryFacade : IDisposable
{  
    #region Single
    public T Single<T>() where T : new()
    {
        return this.Single<T>(this.ConnectionInfo);
    }
    public T Single<T>(AdConnectionInfo connectionInfo) where T : new()
    {
        IList<T> list = this.List<T>(connectionInfo);
        if (list.Count == 0)
        {
            return default(T);
        }
        return list[0];
    }
#endregion

    #region List
    public DataTable List()        
    {
        return List(this.ConnectionInfo);
    }
    public DataTable List(AdConnectionInfo connectionInfo)
    {
        AdDataAccessOptions options = new AdDataAccessOptions();
        options.ConnectionInfo = connectionInfo;
        options.Query = this.ToString();
        options.Transaction = this.Transaction;
        DataSet ds = AdDataAccessComponent.Select(options: options);

        return ds.Tables[0];
    }

     
    public IList<T> List<T>() where T : new()
    {
        return List<T>(this.ConnectionInfo);
    }
    public IList<T> List<T>(AdConnectionInfo connectionInfo) where T : new()
    {
        DataTable dt = List(connectionInfo: connectionInfo);

        IList<T> list = DataTableExtensions.ToList<T>(dt);

        return list;
    }
    public T Scalar<T>() where T : new()
    {
        return Scalar<T>(this.ConnectionInfo);
    }
    public T Scalar<T>(AdConnectionInfo connectionInfo) where T : new()
    {
        AdDataAccessOptions options = new AdDataAccessOptions();
        options.ConnectionInfo = connectionInfo;
        options.Query = this.ToString();
        DataSet ds = AdDataAccessComponent.Select(options: options);

        T item = new T();

        if (ds.Tables[0].Rows.Count == 0)
        {
            return item;
        }

        item = (T)Convert.ChangeType(ds.Tables[0].Rows[0][0], typeof(T)); ;
        //T? item = ds.Tables[0].Rows[0][0].ToNullable<T>();
        return item;
    }

    public Object Scalar(AdConnectionInfo connectionInfo)
    {
        AdDataAccessOptions options = new AdDataAccessOptions();
        options.ConnectionInfo = connectionInfo;
        options.Query = this.ToString();
        DataSet ds = AdDataAccessComponent.Select(options: options);

        if (ds.Tables[0].Rows.Count == 0)
        {
            return null;
        }
        return ds.Tables[0].Rows[0][0];
    }


    #endregion
    public int Execute()
    {
        return this.Execute(this.ConnectionInfo);
    }
    public int Execute(AdConnectionInfo connectionInfo)
    {
        AdDataAccessOptions options = new AdDataAccessOptions();
        options.ConnectionInfo = connectionInfo;
        options.Transaction = this.Transaction;
        options.Query = this.ToString();

        return AdDataAccessComponent.ExecuteNonQuery(options: options);
    }

    public DbTransaction Transaction { get; set; }
    public AdConnectionInfo ConnectionInfo { get; set; }

    public static QueryFacade BeginTransaction(AdConnectionInfo connectionInfo)
    {
        QueryFacade qf = new QueryFacade();
        qf.ConnectionInfo = connectionInfo;        
        Microsoft.Practices.EnterpriseLibrary.Data.Database database = AdDataAccessComponent.CreateDatabase(connectionInfo);
        IDbConnection connection = database.CreateConnection();
        connection.Open();
        qf.Transaction = (DbTransaction)connection.BeginTransaction();        
        return qf;
    }
    public void Dispose()
    {
        if (this.Transaction != null && this.Transaction.Connection != null && this.Transaction.Connection.State == ConnectionState.Open)
        {
            this.Transaction.Rollback();
        }     
    }
     
}

